import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import java.sql.Statement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
import javax.swing.JFrame;
public class BD {


    private DataSource ds;
    private Connection conn;
    MysqlDataSource mds = new MysqlDataSource();



    public void configurar() {
        mds.setUser("root");
        mds.setPassword("rabbit7cam");
        mds.setDatabaseName("abogados");
        mds.setPortNumber(3306);
        mds.setServerName("LocalHost");
        ds = (DataSource) mds;}

    public String abrirConexion() {
        try {
            conn = ds.getConnection();
            return "abrir ok";
        } catch (SQLException sqlExp) {
            return sqlExp.getMessage();
        }
    }

    public String cerrarConexion() {
        try {
            if (conn != null) {
                conn.close();
            }
            return "cerrar ok";
        } catch (SQLException sqlExp) {
            return sqlExp.getMessage();
        }
    }
public List<String> Eliminar(String num) {
        List<String> lista = new ArrayList();
        System.out.println(abrirConexion());
        if (conn != null) {
            try {
                Statement stmt = conn.createStatement();
            

                stmt.executeUpdate(" delete from cliente where nombre='" + num + "'");
       
                stmt.close();
            } catch (SQLException sqlExp) {
                System.out.println(sqlExp.getMessage());
            }
        }
        System.out.println(cerrarConexion());
        return lista;}

    public List<String>[] mostrarLista() {
        ArrayList<String>[] lista =new ArrayList[100];
 for (int i = 0; i < lista.length; i++) {
 lista[i]=new ArrayList<String>();}
        System.out.println(abrirConexion());
        //si la conexion fue exitosa
        if (conn != null) {
            try {

              //  JOptionPane.showMessageDialog(null,+numeroDeTurno);
                //crea consulta sql///////////////
Statement stmt = conn.createStatement();
String query="SELECT * FROM cliente";
                      
//       TABLA cliente
                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()) {
                    lista[0].add(rs.getString("numreg"));}

                 ResultSet rs1 = stmt.executeQuery(query);
                while (rs1.next()) {
                    lista[1].add(rs1.getString("nombre"));}

                  ResultSet rs2 = stmt.executeQuery(query);
                while (rs2.next()) {
                    lista[2].add(rs2.getString("apellidop"));}

                   ResultSet rs3 = stmt.executeQuery(query);
                while (rs3.next()) {
                    lista[3].add(rs3.getString("apellidom"));}

                    ResultSet rs4 = stmt.executeQuery(query);
                while (rs4.next()) {
                    lista[4].add(rs4.getString("tel"));}

                     ResultSet rs5 = stmt.executeQuery(query);
                while (rs5.next()) {
                    lista[5].add(rs5.getString("cel"));}

                      ResultSet rs6 = stmt.executeQuery(query);
                while (rs6.next()) {
                    lista[6].add(rs6.getString("edad"));}

                       ResultSet rs7 = stmt.executeQuery(query);
                while (rs7.next()) {
                    lista[7].add(rs7.getString("domicilio"));}

                        ResultSet rs8 = stmt.executeQuery(query);
                while (rs8.next()) {
                    lista[8].add(rs8.getString("totalpagar"));}

                         ResultSet rs9 = stmt.executeQuery(query);
                while (rs9.next()) {
                    lista[9].add(rs9.getString("anticipo"));}

                          ResultSet rs10 = stmt.executeQuery(query);
                while (rs10.next()) {
                    lista[10].add(rs10.getString("fechaalta"));}

                       ResultSet rs11 = stmt.executeQuery(query);
                while (rs11.next()) {
                    lista[11].add(rs11.getString("fechanacimniento"));}

                   ResultSet rs12 = stmt.executeQuery(query);
                while (rs12.next()) {
                    lista[12].add(rs12.getString("caso"));}

                 ResultSet rs13 = stmt.executeQuery(query);
                while (rs13.next()) {
                    lista[13].add(rs13.getString("observaciones")); }

                 ResultSet rs14 = stmt.executeQuery(query);
                while (rs14.next()) {
                    lista[14].add(rs14.getString("saldo"));}

//           
            }



                catch (SQLException sqlExp) {
                System.out.println(sqlExp.getMessage());}}


            System.out.println(cerrarConexion());
        return lista;
    }
   public List<String>[] busqueda(String apellidop) {
        ArrayList<String>[] lista =new ArrayList[100];
 for (int i = 0; i < lista.length; i++) {
 lista[i]=new ArrayList<String>();}
        System.out.println(abrirConexion());
        //si la conexion fue exitosa
        if (conn != null) {
            try {
//System.out.println("jojojo");
              //  JOptionPane.showMessageDialog(null,+numeroDeTurno);
                //crea consulta sql///////////////
Statement stmt = conn.createStatement();
String query="SELECT * FROM cliente WHERE apellidop='"+apellidop+"'";

//       TABLA cliente
                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()) {
                    lista[0].add(rs.getString("numreg"));}

                 ResultSet rs1 = stmt.executeQuery(query);
                while (rs1.next()) {
                    lista[1].add(rs1.getString("nombre"));}

                  ResultSet rs2 = stmt.executeQuery(query);
                while (rs2.next()) {
                    lista[2].add(rs2.getString("apellidop"));}

                   ResultSet rs3 = stmt.executeQuery(query);
                while (rs3.next()) {
                    lista[3].add(rs3.getString("apellidom"));}

                    ResultSet rs4 = stmt.executeQuery(query);
                while (rs4.next()) {
                    lista[4].add(rs4.getString("tel"));}

                     ResultSet rs5 = stmt.executeQuery(query);
                while (rs5.next()) {
                    lista[5].add(rs5.getString("cel"));}

                      ResultSet rs6 = stmt.executeQuery(query);
                while (rs6.next()) {
                    lista[6].add(rs6.getString("edad"));}

                       ResultSet rs7 = stmt.executeQuery(query);
                while (rs7.next()) {
                    lista[7].add(rs7.getString("domicilio"));}

                        ResultSet rs8 = stmt.executeQuery(query);
                while (rs8.next()) {
                    lista[8].add(rs8.getString("totalpagar"));}

                         ResultSet rs9 = stmt.executeQuery(query);
                while (rs9.next()) {
                    lista[9].add(rs9.getString("anticipo"));}

                          ResultSet rs10 = stmt.executeQuery(query);
                while (rs10.next()) {
                    lista[10].add(rs10.getString("fechaalta"));}

                       ResultSet rs11 = stmt.executeQuery(query);
                while (rs11.next()) {
                    lista[11].add(rs11.getString("fechanacimniento"));}

                   ResultSet rs12 = stmt.executeQuery(query);
                while (rs12.next()) {
                    lista[12].add(rs12.getString("caso"));}

                 ResultSet rs13 = stmt.executeQuery(query);
                while (rs13.next()) {
                    lista[13].add(rs13.getString("observaciones")); }

                 ResultSet rs14 = stmt.executeQuery(query);
                while (rs14.next()) {
                    lista[14].add(rs14.getString("saldo"));}

//
            }



                catch (SQLException sqlExp) {
                System.out.println(sqlExp.getMessage());}}


            System.out.println(cerrarConexion());
        return lista;
    }
     public List<String>[] busquedaregistro(String numreg) {
        ArrayList<String>[] lista =new ArrayList[100];
 for (int i = 0; i < lista.length; i++) {
 lista[i]=new ArrayList<String>();}
        System.out.println(abrirConexion());
        //si la conexion fue exitosa
        if (conn != null) {
            try {

//crea consulta sql///////////////
Statement stmt = conn.createStatement();
String query=" select nombre, apellidop,edad,observaciones from cliente where numreg='"+numreg+"'";
//       TABLA cliente
      
                 ResultSet rs1 = stmt.executeQuery(query);
                while (rs1.next()) {
                    lista[0].add(rs1.getString("nombre"));}

                  ResultSet rs2 = stmt.executeQuery(query);
                while (rs2.next()) {
                    lista[1].add(rs2.getString("apellidop"));}
                 ResultSet rs3 = stmt.executeQuery(query);
                while (rs3.next()) {
                    lista[2].add(rs3.getString("edad")); }
                  ResultSet rs14 = stmt.executeQuery(query);
                while (rs14.next()) {
                    lista[3].add(rs14.getString("observaciones")); }

            }



                catch (SQLException sqlExp) {
                System.out.println(sqlExp.getMessage());}}


            System.out.println(cerrarConexion());
        return lista;
    }

 
 
    public static void main (String [] args){
    BD bd =new BD ();
    bd.configurar();
    }
public List<String> Agregar(String a,String b,String c,String d,String e,String f,String g,String h,String i,String j,String k,String l,String m, String n,String o) {
        List<String> lista = new ArrayList();
        System.out.println(abrirConexion());
        if (conn != null) {
            try {
                Statement stmt = conn.createStatement();
  


                stmt.executeUpdate("insert into cliente values ('"+a+"','"+b+"','"+c+"','"+d+"','"+e+"','"+f+"','"+g+"','"+h+"','"+i+"','"+j+"','"+k+"','"+l+"','"+m+"','"+n+"','"+o+"')");
                stmt.close();
            } catch (SQLException sqlExp) {
                System.out.println(sqlExp.getMessage());
            }
        }
        System.out.println(cerrarConexion());
        return lista;
    }


 public List<String> Modificar(String nuevo, String registro,String columna_seleccionada) {
        List<String> lista = new ArrayList();
        System.out.println(abrirConexion());
        if (conn != null) {
            try {
                Statement stmt = conn.createStatement();

//                stmt.executeUpdate("update materia set materias = '" + nuevoNombre + "' where materias = '" + EliminarDato + "'");

                stmt.executeUpdate("update cliente set "+columna_seleccionada+"='"+nuevo+"' where numreg='"+ registro+"'"  );

                stmt.close();
            } catch (SQLException sqlExp) {
                System.out.println(sqlExp.getMessage());
            }
        }
        System.out.println(cerrarConexion());
        return lista;
    }



}
